
package com.feib.soeasy.dao.jdbc;

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.ColumnMapRowMapper;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;
import org.springframework.util.Assert;

import com.feib.soeasy.dao.Soez5006Dao;
import com.feib.soeasy.dao.jdbc.Soez5008JdbcDao.RowMapperResultSetExtractor;
import com.feib.soeasy.model.Group;
import com.feib.soeasy.model.Payment;
import com.feib.soeasy.model.WriteOffPayment;
import com.feib.soeasy.pagination.PaginatedList;
import com.feib.soeasy.util.CodeTableUtil;
import com.feib.soeasy.util.DateUtil;
import com.feib.soeasy.util.UserUtil;


/**
 * @title (#)Soez5006JdbcDao.java<br>
 * @description <br>
 * @author Jimmy Liu<br>
 * @version 1.0.0 2010/11/25
 * @copyright Far Eastern International Bank Copyright (c) 2010<br>
 * @2011/3/14 create by Jimmy Liu<br>
 */
@Repository("soez5006Dao")
public class Soez5006JdbcDao extends JdbcDaoSupport implements Soez5006Dao {
	protected Logger logger = LoggerFactory.getLogger(this.getClass());

	@Resource(name = "dataSource")
	public void setMyDataSource(DataSource dataSource) {
		super.setDataSource(dataSource); 
	}


	@Override
	public List queryFeedBackLog(Group group , Date adayS, Date adayE, String channel, String commissionCode)
	{
		StringBuffer sql = new StringBuffer();
		sql.append("select f.*,convert(varchar(8),f.fbtime,112) as fbtime from feedback_log f , real_account r ");
		sql.append("where barcdoe1 is not null ");
		sql.append("and r.virtual_account_no = substring(f.barcdoe2, 1, len(r.virtual_account_no)) ");
		sql.append("and r.group_pk = ? ");
		sql.append("and f.err_msg is not null ");
		sql.append("and f.feedback_type = 2 ");

		if(channel != null)
			sql.append("and f.channel = ? ");

		sql.append("and convert(varchar(8),f.fbtime,112) between ?  and ? ");

		if(commissionCode != null)
			sql.append("and  substring(F.barcdoe1, 7,3) = ? ");

		List<Object> args = new ArrayList<Object>();
		args.add(group.getGroupPk());

		if(channel != null){
			if(channel.equals("D"))
				args.add("7111111");
			else if(channel.equals("E"))
				args.add("TFM");
			else if(channel.equals("F"))
				args.add("HILIFE");
			else if(channel.equals("G"))
				args.add("OKCVS");
			else if(channel.equals("H"))
				args.add("POST");
		}

		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
		args.add(dateFormat.format(adayS));
		args.add(dateFormat.format(adayE));

		if(commissionCode != null)
			args.add(commissionCode);

		logger.debug("5006queryFeedBackLog sql={};  args={}", sql, args);

		return this.getJdbcTemplate().queryForList(sql.toString(), args.toArray());
	}


	public List<Map<String, Object>> findCommissionCodesByFormatDefPk(Long formatDefPk)
	{
		StringBuffer sql = new StringBuffer();
		sql.append("select distinct");
		sql.append(" COMMISSION_CODE_TABLE.COMMISSION_CODE_PK");
		sql.append(", COMMISSION_CODE_TABLE.COMMISSION_CODE");
		sql.append(", COMMISSION_CODE_TABLE.COMMISSION_CODE_NAME");
		sql.append(", COMMISSION_CODE_TABLE.AMOUNT_SCOPE_S");
		sql.append(", COMMISSION_CODE_TABLE.AMOUNT_SCOPE_E");
		sql.append(", COMMISSION_CODE_TABLE.COMMISSION_TYPE"); // 手續費類別 I:內含 , O:外加
		sql.append(", COMMISSION_CODE_TABLE.COMMISSION_AMOUNT");
		sql.append(", COMMISSION_CODE_TABLE.BANK_AMOUNT"); //本行帳務處理費
		sql.append(", COMMISSION_CODE_TABLE.PAYER_AMOUNT"); // 繳款者負擔(外加時有用)
		sql.append(", FORMAT_DEF_COMMISSION.FORMAT_DEF_PK");
		sql.append(" from COMMISSION_CODE_TABLE");
		sql.append(" inner join FORMAT_DEF_COMMISSION ");
		sql.append(" on COMMISSION_CODE_TABLE.COMMISSION_CODE_PK = FORMAT_DEF_COMMISSION.COMMISSION_CODE_PK");
		sql.append(" where FORMAT_DEF_COMMISSION.FORMAT_DEF_PK = ?");
		return this.getJdbcTemplate().queryForList(sql.toString(), new Object[]{formatDefPk});
	}

	/**
	 * 超商代收統計表-以業者查詢
	 * @param groupNo 業者代號
	 * @param adayS 銷帳日期起
	 * @param adayE 銷帳日期迄
	 * @author Jimmy Liu
	 * @since 2012/02/16
	 * @return
	 */
	public List<Map<String, Object>> queryStoreStatisticsByGroup(String groupNo, String commissionCode, Date adayS, Date adayE)
	{
		StringBuffer sql = new StringBuffer();
		List<Object> args = new ArrayList<Object>();

		sql.append("\r\n");
		sql.append("select group_no, group_name, account_no, count(group_no) as rdCount, sum(amount) as amount, sum(real_pay_amount) as real_pay_amount").append("\r\n");
		sql.append("from(").append("\r\n");

		sql.append("select g.group_no, g.group_name, r.account_no, w.amount").append("\r\n");
		sql.append(", (w.amount - (").append("\r\n");
		sql.append("      CASE W.CHANNEL WHEN 'H' THEN ").append("\r\n");
		sql.append("           CASE WHEN w.amount <= 0 THEN 0").append("\r\n");
		sql.append("                WHEN w.amount <= 100 THEN ISNULL(G.POST_LEVEL1, 0 )").append("\r\n");    	
		sql.append("                WHEN w.amount <= 1000 THEN ISNULL(G.POST_LEVEL2 , 0 )").append("\r\n");
		sql.append("                ELSE ISNULL(G.POST_LEVEL3, 0 )").append("\r\n");
		sql.append("           END").append("\r\n");
		sql.append("      ELSE c.group_amount END) ) as  real_pay_amount").append("\r\n");
		sql.append("from payment p, write_off_payment w, real_account r, app_group g, commission_code_table c").append("\r\n");
		sql.append("where p.payment_pk = w.payment_pk").append("\r\n");
		sql.append("and p.group_pk = g.group_pk").append("\r\n");
		sql.append("and r.group_pk = g.group_pk").append("\r\n");
		sql.append("and p.payment_status = 5").append("\r\n");
		sql.append("and substring(p.virtual_account,1, len(r.VIRTUAL_ACCOUNT_NO)) = r.VIRTUAL_ACCOUNT_NO").append("\r\n");
		sql.append("and c.commission_code  = (case when p.total_amount =w.amount then substring(p.barcode_stores_1 , 7, 3) else  (case  when p.barcode_stores_1_loan != null then  substring(p.barcode_stores_1_loan, 7, 3) else substring(p.barcode_stores_1, 7, 3)  end  )   end )").append("\r\n");
		sql.append("and w.channel in ('D', 'E', 'F', 'G', 'H')").append("\r\n");
		if (StringUtils.isNotEmpty(groupNo))
		{
			sql.append("and g.GROUP_NO = ?").append("\r\n");
			args.add(groupNo);
		}

		if (StringUtils.isNotEmpty(commissionCode))
		{
			sql.append("and c.commission_code = ? ").append("\r\n");
			args.add(commissionCode);
		}

		if (null != adayS)
		{
			sql.append("and convert(varchar(8),w.write_off_date,112) between convert(varchar(8),?,112) and convert(varchar(8),?,112) ").append("\r\n");
			args.add(adayS);
			args.add(adayE);
		}

		sql.append("\r\n");
		sql.append("union all ").append("\r\n");
		sql.append("\r\n");

		// 四大超商，不含郵局
		sql.append("select g.group_no, g.group_name, r.account_no, f.TXAMT as amount").append("\r\n");
		sql.append(", (f.TXAMT - c.group_amount) as  real_pay_amount").append("\r\n");
		sql.append("from feedback_log f , real_account r , app_group g ,  commission_code_table c").append("\r\n");
		sql.append("where r.group_pk = g.group_pk").append("\r\n");
		sql.append("and f.barcdoe1 is not null ").append("\r\n");
		sql.append("and f.err_msg is not null").append("\r\n");
		sql.append("and f.feedback_type = 2").append("\r\n");
		sql.append("and r.virtual_account_no = substring(f.barcdoe2, 1, len(r.virtual_account_no)) ").append("\r\n");
		sql.append("and c.commission_code  = substring(f.barcdoe1, 7, 3)").append("\r\n");
		sql.append("and f.channel in ('7111111', 'TFM', 'HILIFE', 'OKCVS')").append("\r\n");
		if (StringUtils.isNotEmpty(groupNo))
		{
			sql.append("and g.GROUP_NO = ?").append("\r\n");
			args.add(groupNo);
		}

		if (StringUtils.isNotEmpty(commissionCode))
		{
			sql.append("and c.commission_code = ? ").append("\r\n");
			args.add(commissionCode);
		}

		if (null != adayS)
		{
			sql.append("and convert(varchar(8),f.fbtime,112) between ?  and ? ").append("\r\n");
			args.add(adayS);
			args.add(adayE);
		}

		sql.append("\r\n");
		sql.append("union all ").append("\r\n");
		sql.append("\r\n");

		// 只有郵局
		sql.append("select g.group_no, g.group_name, r.account_no, f.TXAMT as amount").append("\r\n");
		sql.append(", (f.TXAMT - (").append("\r\n");
		sql.append("              CASE WHEN f.TXAMT <= 0 THEN 0").append("\r\n");
		sql.append("                   WHEN f.TXAMT <= 100 THEN ISNULL(G.POST_LEVEL1, 0 )").append("\r\n");    	
		sql.append("                   WHEN f.TXAMT <= 1000 THEN ISNULL(G.POST_LEVEL2 , 0 )").append("\r\n");
		sql.append("                   ELSE ISNULL(G.POST_LEVEL3, 0 )").append("\r\n");
		sql.append("              END").append("\r\n");
		sql.append("             ) )as  real_pay_amount").append("\r\n");
		sql.append("from feedback_log f , real_account r , app_group g").append("\r\n");
		sql.append("where r.group_pk = g.group_pk").append("\r\n");
		sql.append("and f.barcdoe3 is not null ").append("\r\n");
		sql.append("and f.err_msg is not null").append("\r\n");
		sql.append("and f.feedback_type = 2").append("\r\n");
		sql.append("and r.virtual_account_no = substring(f.barcdoe3, 1, len(r.virtual_account_no)) ").append("\r\n");
		sql.append("and f.channel = 'POST' ").append("\r\n");
		if (StringUtils.isNotEmpty(groupNo))
		{
			sql.append("and g.GROUP_NO = ?").append("\r\n");
			args.add(groupNo);
		}

		if (null != adayS)
		{
			sql.append("and convert(varchar(8),f.fbtime,112) between ?  and ? ").append("\r\n");
			args.add(adayS);
			args.add(adayE);
		}

		sql.append("").append("\r\n");


		sql.append(") tempA").append("\r\n");
		sql.append("group by group_no, group_name, account_no").append("\r\n");
		sql.append("order by group_no, group_name, account_no").append("\r\n");
		sql.append("\r\n");

		logger.debug("SOEZ5006超商代收統計查詢-以帳單業者查詢SQL:{}args{}", sql.toString(), args);

		return this.getJdbcTemplate().queryForList(sql.toString(), args.toArray());

	}

	/**
	 * 找出銷帳日期區間內的Commission Code SQL
	 * @param group 業者代號
	 * @param channel 超商代號
	 * @param adayS 銷帳日期起
	 * @param adayE 銷帳日期迄
	 * @author Jimmy Liu
	 * @since 2012/02/16
	 * @return
	 */
	private void getStoreCommissionCodeSQL(StringBuffer sql, List<Object> args, Group group, String channel, Date adayS, Date adayE)
	{
		sql.append("select (case when p.total_amount = w.amount then substring(p.barcode_stores_1, 7, 3) ").append("\r\n");
		sql.append("             else (case when p.barcode_stores_1_loan != null then  substring(p.barcode_stores_1_loan, 7, 3) ").append("\r\n");
		sql.append("                        else substring(p.barcode_stores_1, 7, 3)  ").append("\r\n");
		sql.append("                   end ) ").append("\r\n");
		//jeff
		sql.append("        end ) as commission_code  ").append("\r\n");
		sql.append("from (SELECT * FROM payment WHERE payment_status = 5 ").append("\r\n");
		if (null!=group.getGroupPk())
		{
			sql.append("and GROUP_PK = ?").append("\r\n");
			args.add(group.getGroupPk());
		}
		sql.append(") AS p ,").append("\r\n");
		sql.append(" (select * from write_off_payment where 1=1 ").append("\r\n");
		if (StringUtils.isNotEmpty(channel))
		{
			sql.append("and channel = ? ").append("\r\n");
			args.add(channel);
		}
		else
		{
			sql.append("and channel in ('D', 'E', 'F', 'G', 'H')").append("\r\n");
		}

		if (null != adayS)
		{
			sql.append("and convert(varchar(8),write_off_date,112) between convert(varchar(8),?,112) and convert(varchar(8),?,112)  ").append("\r\n");
			args.add(adayS);
			args.add(adayE);
		}		
		sql.append(")as w, ").append("\r\n");

		sql.append("app_group g, commission_code_table c ").append("\r\n");
		sql.append("where p.payment_pk = w.payment_pk ").append("\r\n");
		sql.append("and p.group_pk = g.group_pk ").append("\r\n");
		//TODO
		//sql.append("and p.payment_status = 5 ").append("\r\n");
		sql.append("and c.commission_code  = (case when p.total_amount =w.amount then substring(p.barcode_stores_1 , 7, 3) else  (case  when p.barcode_stores_1_loan != null then  substring(p.barcode_stores_1_loan, 7, 3) else substring(p.barcode_stores_1, 7, 3)  end  )   end )").append("\r\n");



		sql.append(" ").append("\r\n");

		/*未消帳成功的資料也要納入*/
		sql.append("union ").append("\r\n");
		sql.append(" ").append("\r\n");
		//jeff
		sql.append("select  substring(f.barcdoe1, 7, 3) as  commission_code ").append("\r\n");
		sql.append("from (select * from feedback_log  ").append("\r\n");
		sql.append("where feedback_type = 2 ").append("\r\n");       
		sql.append("and barcdoe1 is not null  ").append("\r\n");
		sql.append("and err_msg is not null ").append("\r\n"); 
		if (StringUtils.isNotEmpty(channel))
		{
			sql.append("and channel = ? ").append("\r\n");
			if(channel.equals("D"))
				args.add("7111111");
			else if(channel.equals("E"))
				args.add("TFM");
			else if(channel.equals("F"))
				args.add("HILIFE");
			else if(channel.equals("G"))
				args.add("OKCVS");
			else if(channel.equals("H"))
				args.add("POST");
		}
		else
		{
			sql.append("and channel in ('7111111', 'TFM', 'HILIFE', 'OKCVS', 'POST')").append("\r\n");
		}

		if (null != adayS)
		{
			sql.append("and convert(varchar(8),fbtime,112) between convert(varchar(8),?,112)  and convert(varchar(8),?,112)  ").append("\r\n");
			args.add(adayS);
			args.add(adayE);
		}
		sql.append(")f , real_account r , app_group g ").append("\r\n");
		sql.append("where r.group_pk = g.group_pk ").append("\r\n");
		sql.append("and r.virtual_account_no = substring(f.barcdoe2, 1, len(r.virtual_account_no))  ").append("\r\n");


		if (null!=group.getGroupPk())
		{
			sql.append("and g.GROUP_PK = ?").append("\r\n");
			args.add(group.getGroupPk());
		}


		sql.append(" order by commission_code ");
	}

	/**
	 * 找出銷帳日期區間內的Commission Code
	 * @param group 業者代號
	 * @param channel 超商代號
	 * @param adayS 銷帳日期起
	 * @param adayE 銷帳日期迄
	 * @author Jimmy Liu
	 * @since 2012/02/16
	 * @return
	 * TODO 化面查詢通路
	 */
	public List<String> queryStoreCommissionCode(Group group, String channel, Date adayS, Date adayE)
	{
		List<Object> args = new ArrayList<Object>();
		StringBuffer sql = new StringBuffer();

		getStoreCommissionCodeSQL(sql, args, group, channel, adayS, adayE);
		
		logger.debug("SOEZ5006超商代收統計查詢-queryStoreCommissionCodeSQL:{}args{}", sql.toString(), args);
        //jeff
		List<Map<String, Object>> rds =  this.getJdbcTemplate().queryForList(sql.toString(), args.toArray());
		List<String> result = new ArrayList<String>();
		
		logger.debug("SOEZ5006超商代收統計查詢-rdS{}", rds);
		
		for (Map<String, Object> rd : rds){
			logger.debug("SOEZ5006超商代收統計查詢-rd{}", rd);
			result.add((String)rd.get("commission_code"));
		}
		logger.debug("SOEZ5006超商代收統計查詢-result{}", result);
		return result;
	}

	/**
	 * 以通路跟CommissionCode統計
	 * @param group
	 * @param channel
	 * @param commissionCode
	 * @param adayS
	 * @param adayE
	 * @return
	 */
	public List<Map<String, Object>> queryStoreStatistics(Group group, String channel, String commissionCode, Date adayS, Date adayE)
	{
		StringBuffer sql = new StringBuffer();
		List<Object> args = new ArrayList<Object>();

		sql.append("\r\n");
		sql.append("select group_no, group_name, count(group_no) as rdCount, sum(amount) as amount, sum(real_pay_amount) as real_pay_amount").append("\r\n");
		sql.append("from(").append("\r\n");

		sql.append("select g.group_no, g.group_name, w.amount").append("\r\n");
		sql.append(", (w.amount - (").append("\r\n");
		sql.append("      CASE W.CHANNEL WHEN 'H' THEN ").append("\r\n");
		sql.append("           CASE WHEN w.amount <= 0 THEN 0").append("\r\n");
		sql.append("                WHEN w.amount <= 100 THEN ISNULL(G.POST_LEVEL1, 0 )").append("\r\n");    	
		sql.append("                WHEN w.amount <= 1000 THEN ISNULL(G.POST_LEVEL2 , 0 )").append("\r\n");
		sql.append("                ELSE ISNULL(G.POST_LEVEL3, 0 )").append("\r\n");
		sql.append("           END").append("\r\n");
		sql.append("      ELSE c.group_amount END) ) as  real_pay_amount").append("\r\n");
		sql.append("from (SELECT * FROM payment WHERE payment_status = 5 ").append("\r\n");
		if (null!=group.getGroupPk())
		{
			sql.append("and GROUP_PK = ?").append("\r\n");
			args.add(group.getGroupPk());
		}
		sql.append(") AS p ,").append("\r\n");
		sql.append(" (select * from write_off_payment where 1=1 ").append("\r\n");
		if (StringUtils.isNotEmpty(channel))
		{
			sql.append("and channel = ? ").append("\r\n");
			args.add(channel);
		}
		else
		{
			sql.append("and channel in ('D', 'E', 'F', 'G', 'H')").append("\r\n");
		}

		if (null != adayS)
		{
			sql.append("and convert(varchar(8),write_off_date,112) between convert(varchar(8),?,112) and convert(varchar(8),?,112)  ").append("\r\n");
			args.add(adayS);
			args.add(adayE);
		}		
		sql.append(")as w, ").append("\r\n");

		sql.append("real_account r, app_group g, commission_code_table c  ").append("\r\n");	sql.append("where p.payment_pk = w.payment_pk").append("\r\n");
		//TODO 
		//sql.append("and  ").append("\r\n");
		sql.append("and p.group_pk = g.group_pk").append("\r\n");
		sql.append("and r.group_pk = g.group_pk").append("\r\n");   	
		sql.append("and substring(p.virtual_account,1, len(r.VIRTUAL_ACCOUNT_NO)) = r.VIRTUAL_ACCOUNT_NO").append("\r\n");
		sql.append("and c.commission_code  = (case when p.total_amount =w.amount then substring(p.barcode_stores_1 , 7, 3) else  (case  when p.barcode_stores_1_loan != null then  substring(p.barcode_stores_1_loan, 7, 3) else substring(p.barcode_stores_1, 7, 3)  end  )   end )").append("\r\n");



		if (StringUtils.isNotEmpty(commissionCode))
		{
			sql.append("and c.commission_code = ? ").append("\r\n");
			args.add(commissionCode);
		}



		sql.append("\r\n");
		// 四大超商，不含郵局
		if (StringUtils.isEmpty(channel) || ( StringUtils.isNotEmpty(channel) && !"H".equals(channel)) )
		{
			sql.append("union all ").append("\r\n");
			sql.append("\r\n");

			sql.append("select g.group_no, g.group_name, f.TXAMT as amount").append("\r\n");
			sql.append(", (f.TXAMT - c.group_amount ) as  real_pay_amount").append("\r\n");
			sql.append("from (select * from feedback_log  ").append("\r\n");
			sql.append("where feedback_type = 2 ").append("\r\n");       
			sql.append("and barcdoe1 is not null  ").append("\r\n");
			sql.append("and err_msg is not null ").append("\r\n"); 
			if (StringUtils.isNotEmpty(channel))
			{
				sql.append("and channel = ? ").append("\r\n");
				if(channel.equals("D"))
					args.add("7111111");
				else if(channel.equals("E"))
					args.add("TFM");
				else if(channel.equals("F"))
					args.add("HILIFE");
				else if(channel.equals("G"))
					args.add("OKCVS");
				else if(channel.equals("H"))
					args.add("POST");
			}
			else
			{
				sql.append("and channel in ('7111111', 'TFM', 'HILIFE', 'OKCVS', 'POST')").append("\r\n");
			}

			if (null != adayS)
			{
				sql.append("and convert(varchar(8),fbtime,112) between ?  and ?  ").append("\r\n");
				args.add(adayS);
				args.add(adayE);
			}
			sql.append(")f ,real_account r, app_group g, commission_code_table c ").append("\r\n");
			sql.append("where r.group_pk = g.group_pk").append("\r\n");
			sql.append("and r.virtual_account_no = substring(f.barcdoe2, 1, len(r.virtual_account_no)) ").append("\r\n");
			sql.append("and c.commission_code  = substring(f.barcdoe1, 7, 3)").append("\r\n");

			if (null != group.getGroupPk())
			{
				sql.append("and r.group_pk = ?").append("\r\n");
				args.add(group.getGroupPk());
			}

			if (StringUtils.isNotEmpty(commissionCode))
			{
				sql.append("and c.commission_code = ? ").append("\r\n");
				args.add(commissionCode);
			}

		}

		sql.append("\r\n");

		// 只有郵局
		if  ( StringUtils.isEmpty(commissionCode) && (StringUtils.isEmpty(channel) || ( StringUtils.isNotEmpty(channel) && "H".equals(channel)) ) )
		{
			sql.append("union all ").append("\r\n");
			sql.append("\r\n");

			sql.append("select g.group_no, g.group_name, f.TXAMT as amount").append("\r\n");
			sql.append(", (f.TXAMT - (").append("\r\n");    	
			sql.append("              CASE WHEN f.TXAMT <= 0 THEN 0").append("\r\n");
			sql.append("                   WHEN f.TXAMT <= 100 THEN ISNULL(G.POST_LEVEL1, 0 )").append("\r\n");    	
			sql.append("                   WHEN f.TXAMT <= 1000 THEN ISNULL(G.POST_LEVEL2 , 0 )").append("\r\n");
			sql.append("                   ELSE ISNULL(G.POST_LEVEL3, 0 )").append("\r\n");
			sql.append("              END").append("\r\n");
			sql.append("            ) )as  real_pay_amount").append("\r\n");
			sql.append("from (select * from feedback_log  ").append("\r\n");
			sql.append("where feedback_type = 2 ").append("\r\n");       
			sql.append("and channel = 'POST'").append("\r\n");
			sql.append("and barcdoe3 is not null ").append("\r\n");
			sql.append("and err_msg is not null").append("\r\n");
			if (null != adayS)
			{
				sql.append("and convert(varchar(8),fbtime,112) between ?  and ?  ").append("\r\n");
				args.add(adayS);
				args.add(adayE);
			}
			sql.append(")f ,real_account r, app_group g, commission_code_table c ").append("\r\n");

			sql.append("where r.group_pk = g.group_pk").append("\r\n");
			sql.append("and r.virtual_account_no = substring(f.barcdoe3, 1, len(r.virtual_account_no)) ").append("\r\n");



			if (null != group.getGroupPk())
			{
				sql.append("and r.group_pk = ?").append("\r\n");
				args.add(group.getGroupPk());
			}



		}

		sql.append("").append("\r\n");
		sql.append(") tempA").append("\r\n");
		sql.append("group by group_no, group_name").append("\r\n");
		sql.append("order by group_no, group_name").append("\r\n");
		sql.append("\r\n");

		logger.debug("SOEZ5006超商代收統計查詢-以超商查詢SQL:{}args{}", sql.toString(), args);

		return this.getJdbcTemplate().queryForList(sql.toString(), args.toArray());

	}

	/**
	 * 以通路別跟CommissionCode查詢明細
	 * @param groupNo
	 * @param channel
	 * @param commissionCode
	 * @param adayS
	 * @param adayE
	 * @return
	 */
	public List<Map<String, Object>> queryPaymentByChannelCommission(String groupNo, String channel, String commissionCode, Date adayS, Date adayE)
	{
		StringBuffer sql = new StringBuffer();
		List<Object> args = new ArrayList<Object>();

		sql.append("\r\n");
		sql.append("select group_no, group_name, virtual_account, payer_name, payer_id, tx_date, write_off_date, amount,barcdoe1 ").append("\r\n");
		sql.append("from(").append("\r\n");

		sql.append("select g.group_no, g.group_name, 1 as src_type, p.virtual_account, p.payer_name").append("\r\n");
		sql.append(", (case when g.group_type = 2 then p.student_id else p.payer_id end ) as payer_id").append("\r\n");
		sql.append(", w.tx_date, w.write_off_date, w.amount,p.barcode_stores_1 as barcdoe1 ").append("\r\n");
		sql.append("from (SELECT * FROM payment WHERE payment_status = 5 ").append("\r\n");

		sql.append(") AS p ,").append("\r\n");
		sql.append(" (select * from write_off_payment where 1=1 ").append("\r\n");
		if (StringUtils.isNotEmpty(channel))
		{
			sql.append("and channel = ? ").append("\r\n");
			args.add(channel);
		}
		else
		{
			sql.append("and channel in ('D', 'E', 'F', 'G', 'H')").append("\r\n");
		}

		if (null != adayS)
		{
			sql.append("and convert(varchar(8),write_off_date,112) between convert(varchar(8),?,112) and convert(varchar(8),?,112)  ").append("\r\n");
			args.add(adayS);
			args.add(adayE);
		}		
		sql.append(")as w, ").append("\r\n");

		sql.append("real_account r, app_group g, commission_code_table c ").append("\r\n");sql.append("where p.payment_pk = w.payment_pk").append("\r\n");
		sql.append("and p.group_pk = g.group_pk").append("\r\n");
		sql.append("and r.group_pk = g.group_pk").append("\r\n");
		sql.append("and substring(p.virtual_account,1, len(r.VIRTUAL_ACCOUNT_NO)) = r.VIRTUAL_ACCOUNT_NO").append("\r\n");
		sql.append("and c.commission_code  = (case when p.total_amount =w.amount then substring(p.barcode_stores_1 , 7, 3) else  (case  when p.barcode_stores_1_loan != null then  substring(p.barcode_stores_1_loan, 7, 3) else substring(p.barcode_stores_1, 7, 3)  end  )   end )").append("\r\n");


		if (StringUtils.isNotEmpty(commissionCode))
		{
			sql.append("and c.commission_code = ? ").append("\r\n");
			args.add(commissionCode);
		}
		
		if (StringUtils.isNotEmpty(groupNo))
		{
			sql.append("and g.GROUP_NO = ?").append("\r\n");
			args.add(groupNo);
		}


		sql.append("\r\n");
		// 四大超商，不含郵局
		if (StringUtils.isEmpty(channel) || ( StringUtils.isNotEmpty(channel) && !"H".equals(channel)) )
		{
			sql.append("union all ").append("\r\n");
			sql.append("\r\n");

			sql.append("select g.group_no, g.group_name, 2 as src_type, '銷帳失敗' as virtual_account, null as payer_name").append("\r\n");
			sql.append(", null as payer_id").append("\r\n");
			sql.append(", convert(datetime, f.aday,112) as tx_date, f.FBTIME as write_off_date, f.TXAMT as amount,f.barcdoe1").append("\r\n");
			sql.append("from (select * from feedback_log  ").append("\r\n");
			sql.append("where feedback_type = 2 ").append("\r\n");       
			sql.append("and barcdoe1 is not null  ").append("\r\n");
			sql.append("and err_msg is not null ").append("\r\n"); 
			if (StringUtils.isNotEmpty(channel))
			{
				sql.append("and channel = ? ").append("\r\n");
				if(channel.equals("D"))
					args.add("7111111");
				else if(channel.equals("E"))
					args.add("TFM");
				else if(channel.equals("F"))
					args.add("HILIFE");
				else if(channel.equals("G"))
					args.add("OKCVS");
				else if(channel.equals("H"))
					args.add("POST");
			}
			else
			{
				sql.append("and channel in ('7111111', 'TFM', 'HILIFE', 'OKCVS', 'POST')").append("\r\n");
			}

			if (null != adayS)
			{
				sql.append("and convert(varchar(8),fbtime,112) between ?  and ?  ").append("\r\n");
				args.add(adayS);
				args.add(adayE);
			}
			sql.append(")f ,real_account r, app_group g, commission_code_table c ").append("\r\n");
			sql.append("where r.group_pk = g.group_pk").append("\r\n");
			sql.append("and r.virtual_account_no = substring(f.barcdoe2, 1, len(r.virtual_account_no)) ").append("\r\n");
			sql.append("and c.commission_code  = substring(f.barcdoe1, 7, 3)").append("\r\n");

			if (StringUtils.isNotEmpty(groupNo))
			{
				sql.append("and g.GROUP_NO = ?").append("\r\n");
				args.add(groupNo);
			}

			if (StringUtils.isNotEmpty(commissionCode))
			{
				sql.append("and c.commission_code = ? ").append("\r\n");
				args.add(commissionCode);
			}


		}


		// 只有郵局
		if  ( StringUtils.isEmpty(commissionCode) && (StringUtils.isEmpty(channel) || ( StringUtils.isNotEmpty(channel) && "H".equals(channel)) ) )
		{
			sql.append("union all ").append("\r\n");
			sql.append("\r\n");

			sql.append("select g.group_no, g.group_name, 2 as src_type, '銷帳失敗' as virtual_account, null as payer_name").append("\r\n");
			sql.append(", null as payer_id").append("\r\n");
			sql.append(", convert(datetime, f.aday,112) as tx_date, f.FBTIME as write_off_date, f.TXAMT as amount,null as barcdoe1 ").append("\r\n");
			sql.append("from (select * from feedback_log  ").append("\r\n");
			sql.append("where feedback_type = 2 ").append("\r\n");       
			sql.append("and channel = 'POST'").append("\r\n");
			sql.append("and barcdoe3 is not null ").append("\r\n");
			sql.append("and err_msg is not null").append("\r\n");
			if (null != adayS)
			{
				sql.append("and convert(varchar(8),fbtime,112) between ?  and ?  ").append("\r\n");
				args.add(adayS);
				args.add(adayE);
			}
			sql.append(")f ,real_account r, app_group g, commission_code_table c ").append("\r\n");
			sql.append("where r.group_pk = g.group_pk").append("\r\n");
			sql.append("and r.virtual_account_no = substring(f.barcdoe3, 1, len(r.virtual_account_no)) ").append("\r\n");


			if (StringUtils.isNotEmpty(groupNo))
			{
				sql.append("and g.GROUP_NO = ?").append("\r\n");
				args.add(groupNo);
			}



		}

		sql.append("").append("\r\n");
		sql.append(") tempA").append("\r\n");
		sql.append("order by substring(barcdoe1,len(barcdoe1)-2,3) , virtual_account").append("\r\n");
		sql.append("\r\n");

		logger.debug("SOEZ5006超商代收統計查詢-以超商跟CommissionCode查詢明細SQL:{}args{}", sql.toString(), args);

		return this.getJdbcTemplate().queryForList(sql.toString(), args.toArray());
	}

	public void getStoreGroupPksSQL(StringBuffer sql, List<Object> args, String groupNo, String channel, Date adayS, Date adayE)
	{
		sql.append("select p.group_pk").append("\r\n");
		sql.append("from (SELECT * FROM payment WHERE payment_status = 5) AS p ,  write_off_payment w, app_group g ").append("\r\n");
		sql.append("where p.payment_pk = w.payment_pk ").append("\r\n");
		sql.append("and p.group_pk = g.group_pk ").append("\r\n");
		//TODO
		//sql.append("and p.payment_status = 5 ").append("\r\n");
		if (StringUtils.isNotEmpty(groupNo))
		{
			sql.append("and g.GROUP_NO = ? ").append("\r\n");
			args.add(groupNo);
		}
		if (StringUtils.isNotEmpty(channel))
		{
			sql.append("and w.channel = ? ").append("\r\n");
			args.add(channel);
		}

		if (null != adayS)
		{
			sql.append("and convert(varchar(8),w.write_off_date,112) between convert(varchar(8),?,112) and convert(varchar(8),?,112)  ").append("\r\n");
			args.add(adayS);
			args.add(adayE);
		}

		sql.append(" ").append("\r\n");

		/*未消帳成功的資料也要納入*/
		sql.append("union ").append("\r\n");
		sql.append(" ").append("\r\n");
		sql.append("select r.group_pk ").append("\r\n");
		sql.append("from feedback_log f , real_account r , app_group g ").append("\r\n");
		sql.append("where r.group_pk = g.group_pk ").append("\r\n");
		sql.append("and barcdoe1 is not null  ").append("\r\n");
		sql.append("and r.virtual_account_no = substring(f.barcdoe2, 1, len(r.virtual_account_no)) ").append("\r\n");
		sql.append("and f.feedback_type = 2 ").append("\r\n");
		if (StringUtils.isNotEmpty(groupNo))
		{
			sql.append("and g.GROUP_NO = ?").append("\r\n");
			args.add(groupNo);
		}

		if (StringUtils.isNotEmpty(channel))
		{
			sql.append("and f.channel = ? ").append("\r\n");
			if(channel.equals("D"))
				args.add("7111111");
			else if(channel.equals("E"))
				args.add("TFM");
			else if(channel.equals("F"))
				args.add("HILIFE");
			else if(channel.equals("G"))
				args.add("OKCVS");
			else if(channel.equals("H"))
				args.add("POST");
		}

		if (null != adayS)
		{
			sql.append("and convert(varchar(8),f.fbtime,112) between ?  and ? ").append("\r\n");
			args.add(adayS);
			args.add(adayE);
		}

	}

	/**
	 * 找出銷帳日期區間內的業者PK
	 * @param groupNo 業者代號
	 * @param channel 超商代號
	 * @param adayS 銷帳日期起
	 * @param adayE 銷帳日期迄
	 * @return
	 */
	public List<Long> getStoreGroupPks(String groupNo, String channel, Date adayS, Date adayE)
	{
		List<Object> args = new ArrayList<Object>();
		StringBuffer sql = new StringBuffer();

		getStoreGroupPksSQL(sql, args, groupNo, channel, adayS, adayE);

		List<Map<String, Object>> rds =  this.getJdbcTemplate().queryForList(sql.toString(), args.toArray());
		List<Long> result = new ArrayList<Long>();
		for (Map<String, Object> rd : rds)
			result.add((Long)rd.get("group_pk"));

		return result;
	}


	/** 組合以通路別跟查詢明細sql
	 * @param order TODO

	 */
	public String getQueryPaymentByChannel(Group group ,String[] channels , Date[] dateFroms, Date[] dateTos , List<Object> args, boolean order)
	{

		Map<String, String> channelCode=new HashMap<String, String>();

		channelCode.put("D", "7111111");
		channelCode.put("E", "TFM");
		channelCode.put("F", "HILIFE");
		channelCode.put("G", "OKCVS");
		channelCode.put("H", "POST");

		StringBuffer sql = new StringBuffer();

		args.clear();

		int count=0;
		int lastIndex=0;
		for(int i=0;i<channels.length;i++){
			String channel = channels[i];
			if(!"null".equalsIgnoreCase(channel) && StringUtils.isNotBlank(channel) ){
				count++;
				lastIndex=i;
			}
		}

		sql.append("\r\n");
		sql.append("select group_no, group_name, virtual_account, payer_name, payer_id, tx_date, write_off_date, amount,barcdoe1 ,channel").append("\r\n");
		sql.append("from(").append("\r\n");
		/* 組合子查詢 */
		for(int i=0;i<channels.length;i++)
		{
			String channel = channels[i];
			Date adayS = dateFroms[i];
			Date adayE = dateTos[i];
			if(!"null".equalsIgnoreCase(channel) && StringUtils.isNotBlank(channel)){

				sql.append("select g.group_no, g.group_name, 1 as src_type, p.virtual_account, p.payer_name").append("\r\n");
				sql.append(", (case when g.group_type = 2 then p.student_id else p.payer_id end ) as payer_id").append("\r\n");
				sql.append(",'").append(channel).append("' as channel ").append("\r\n");		
				sql.append(", w.tx_date, w.write_off_date, w.amount,p.barcode_stores_1 as barcdoe1 ").append("\r\n");
				sql.append("from (SELECT * FROM payment WHERE payment_status = 5 ").append("\r\n");

				sql.append(") AS p ,").append("\r\n");
				sql.append(" (select * from write_off_payment where 1=1 ").append("\r\n");
				if (StringUtils.isNotEmpty(channel))
				{
					sql.append("and channel = ? ").append("\r\n");
					args.add(channel);
				}
				else
				{
					sql.append("and channel in ('D', 'E', 'F', 'G', 'H')").append("\r\n");
				}

				if (null != adayS)
				{
					sql.append("and convert(varchar(8),write_off_date,112) between convert(varchar(8),?,112) and convert(varchar(8),?,112) ").append("\r\n");
					args.add(adayS);
					args.add(adayE);
				}		
				sql.append(")as w, ").append("\r\n");

				sql.append("real_account r, app_group g, commission_code_table c ").append("\r\n");sql.append("where p.payment_pk = w.payment_pk").append("\r\n");
				sql.append("and p.group_pk = g.group_pk").append("\r\n");
				sql.append("and r.group_pk = g.group_pk").append("\r\n");
				sql.append("and substring(p.virtual_account,1, len(r.VIRTUAL_ACCOUNT_NO)) = r.VIRTUAL_ACCOUNT_NO").append("\r\n");
				sql.append("and c.commission_code  = (case when p.total_amount =w.amount then substring(p.barcode_stores_1 , 7, 3) else  (case  when p.barcode_stores_1_loan != null then  substring(p.barcode_stores_1_loan, 7, 3) else substring(p.barcode_stores_1, 7, 3)  end  )   end )").append("\r\n");

				if (StringUtils.isNotEmpty(group.getGroupNo()))
				{
					sql.append("and g.GROUP_NO = ?").append("\r\n");
					args.add(group.getGroupNo());
				}



				sql.append("\r\n");
				// 四大超商，不含郵局
				if (StringUtils.isEmpty(channel) || ( StringUtils.isNotEmpty(channel) && !"H".equals(channel)) )
				{
					sql.append("union all ").append("\r\n");
					sql.append("\r\n");

					sql.append("select g.group_no, g.group_name, 2 as src_type, '銷帳失敗' as virtual_account, null as payer_name").append("\r\n");
					sql.append(", null as payer_id").append("\r\n");
					sql.append(",'").append(channel).append("' as channel ").append("\r\n");
					sql.append(", convert(datetime, f.aday,112) as tx_date, f.FBTIME as write_off_date, f.TXAMT as amount,f.barcdoe1").append("\r\n");
					sql.append("from (select * from feedback_log  ").append("\r\n");
					sql.append("where feedback_type = 2 ").append("\r\n");       
					sql.append("and barcdoe1 is not null  ").append("\r\n");
					sql.append("and err_msg is not null ").append("\r\n"); 
					if (StringUtils.isNotEmpty(channel))
					{
						sql.append("and channel = ? ").append("\r\n");
						if(channel.equals("D"))
							args.add("7111111");
						else if(channel.equals("E"))
							args.add("TFM");
						else if(channel.equals("F"))
							args.add("HILIFE");
						else if(channel.equals("G"))
							args.add("OKCVS");
						else if(channel.equals("H"))
							args.add("POST");
					}
					else
					{
						sql.append("and channel in ('7111111', 'TFM', 'HILIFE', 'OKCVS', 'POST')").append("\r\n");
					}

					if (null != adayS)
					{
						sql.append("and convert(varchar(8),fbtime,112) between ?  and ?  ").append("\r\n");
						args.add(adayS);
						args.add(adayE);
					}
					sql.append(")f ,real_account r, app_group g, commission_code_table c ").append("\r\n");
					sql.append("where r.group_pk = g.group_pk").append("\r\n");
					sql.append("and r.virtual_account_no = substring(f.barcdoe2, 1, len(r.virtual_account_no)) ").append("\r\n");
					sql.append("and c.commission_code  = substring(f.barcdoe1, 7, 3)").append("\r\n");

					if (StringUtils.isNotEmpty(group.getGroupNo()))
					{
						sql.append("and g.GROUP_NO = ?").append("\r\n");
						args.add(group.getGroupNo());
					}



				}


				// 只有郵局
				if  ( ( StringUtils.isNotEmpty(channel) && "H".equals(channel))  )
				{
					sql.append("union all ").append("\r\n");
					sql.append("\r\n");

					sql.append("select g.group_no, g.group_name, 2 as src_type, '銷帳失敗' as virtual_account, null as payer_name").append("\r\n");
					sql.append(", null as payer_id").append("\r\n");
					sql.append(",'").append(channel).append("' as channel ").append("\r\n");
					sql.append(", convert(datetime, f.aday,112) as tx_date, f.FBTIME as write_off_date, f.TXAMT as amount,null as barcdoe1 ").append("\r\n");
					sql.append("from (select * from feedback_log  ").append("\r\n");
					sql.append("where feedback_type = 2 ").append("\r\n");       
					sql.append("and channel = 'POST'").append("\r\n");
					sql.append("and barcdoe3 is not null ").append("\r\n");
					sql.append("and err_msg is not null").append("\r\n");
					if (null != adayS)
					{
						sql.append("and convert(varchar(8),fbtime,112) between ?  and ?  ").append("\r\n");
						args.add(adayS);
						args.add(adayE);
					}
					sql.append(")f ,real_account r, app_group g, commission_code_table c ").append("\r\n");
					sql.append("where r.group_pk = g.group_pk").append("\r\n");
					sql.append("and r.virtual_account_no = substring(f.barcdoe3, 1, len(r.virtual_account_no)) ").append("\r\n");


					if (StringUtils.isNotEmpty(group.getGroupNo()))
					{
						sql.append("and g.GROUP_NO = ?").append("\r\n");
						args.add(group.getGroupNo());
					}

				

				}
				
				/* 串各群組 畫面可能會跳號選取 通路*/
				/* 不跳號選取通路時  INDEX比實際選取個數小的狀況下要加union 表示後面還有要串的資料   */
				if(i<= count && i<lastIndex){
					sql.append(" union all ");
				}
				/* 跳號選取通路時  INDEX比實際選取個數大且不是最後一筆的狀況下要加union 表示後面還有要串的資料   */
				if(i>count && i<lastIndex){
					sql.append(" union all ");
				}
			}  	

		}

		sql.append("").append("\r\n");
		sql.append(") tempA").append("\r\n");
		if(order)
			sql.append("order by channel,substring(barcdoe1,len(barcdoe1)-2,3), virtual_account").append("\r\n");
		
		sql.append("\r\n");

		logger.debug("getQueryPaymentByChannelCommission SQL="+sql+"; args="+args);
		return sql.toString();  
	}


	/** jeff 取得明細(根據群組)*/
	public PaginatedList<Map<String,Object>> queryPaymentByChannel(Group group ,String[] channels , Date[] dateFroms, Date[] dateTos , PaginatedList<Map<String, Object>> paginationConfig)
	{
		List<Object> args = new ArrayList<Object>();
		String sql = getQueryPaymentByChannel(group, channels, dateFroms, dateTos, args, true);
		logger.debug("getQueryPaymentByChannel SQL="+sql+"; args="+args);

		RowMapper<Map<String, Object>> rowMapper = new ColumnMapRowMapper();


		PaginatedList<Map<String, Object>> result = new PaginatedList<Map<String,Object>>();
		if(-1==paginationConfig.getPageSize()){
			result.setPaginatedList(this.getJdbcTemplate().queryForList(sql.toString(), args.toArray()));
			return result;
		}
		result.setPageSize(paginationConfig.getPageSize());
		result.setPageNumber(paginationConfig.getPageNumber());
		result.setSortCriterion(paginationConfig.getSortCriterion());
		result.setSortDirection(paginationConfig.getSortDirection());

		RowMapperResultSetExtractor<Map<String, Object>> extracotr = new RowMapperResultSetExtractor<Map<String, Object>>(rowMapper, result);
		this.getJdbcTemplate().query(sql.toString(), args.toArray(), extracotr);
		/* 0644*/
		if(extracotr.getPaginatedList().isLastPage()){
			List<Object> argSum = new ArrayList<Object>();
			String sqlSum=querySumTotalPaymentByChannelSql(group, channels, dateFroms, dateTos, argSum);
			Map<String,Object> SumTotal=this.getJdbcTemplate().queryForMap(sqlSum.toString(), argSum.toArray());
			extracotr.getPaginatedList().setTotalObject(SumTotal);
		}
		return extracotr.getPaginatedList();


	}


	/** 總計明細用的總計 
	 * 顯示在最下方,最後一頁再顯示
	 * 
	 * */
	public String querySumTotalPaymentByChannelSql(Group group ,String[] channels , Date[] dateFroms, Date[] dateTos , List<Object> args){	
		StringBuffer sql = new StringBuffer();
		sql.append("select  count(*) as totalCount ,sum(amount) as totalAmount   from ( ");
		sql.append(getQueryPaymentByChannel(group, channels, dateFroms, dateTos, args, false));
		sql.append(" )  a  where 1=1 ");		
		logger.debug("querySumTotalGroupFeedbackLogSql SQL="+sql+"; args="+args);

		/*釋放資源 */		
		return sql.toString();
	};


	/** jeff 取得明細(根據群組)*/
	public PaginatedList<Map<String,Object>> queryPaymentByChannelCommission(Group group ,String channel ,String code, Date qryDateFrom, Date qryDateTo , PaginatedList<Map<String, Object>> paginationConfig)
	{
		List<Object> args = new ArrayList<Object>();
		String sql = getQueryPaymentByChannelCommission(group.getGroupNo() , channel , code,  qryDateFrom,  qryDateTo,args, true);
		logger.debug("getQueryPaymentByChannelCommission SQL="+sql+"; args="+args);

		RowMapper<Map<String, Object>> rowMapper = new ColumnMapRowMapper();


		PaginatedList<Map<String, Object>> result = new PaginatedList<Map<String,Object>>();
		if(-1==paginationConfig.getPageSize()){
			result.setPaginatedList(this.getJdbcTemplate().queryForList(sql.toString(), args.toArray()));
			return result;
		}
		result.setPageSize(paginationConfig.getPageSize());
		result.setPageNumber(paginationConfig.getPageNumber());
		result.setSortCriterion(paginationConfig.getSortCriterion());
		result.setSortDirection(paginationConfig.getSortDirection());

		RowMapperResultSetExtractor<Map<String, Object>> extracotr = new RowMapperResultSetExtractor<Map<String, Object>>(rowMapper, result);
		this.getJdbcTemplate().query(sql.toString(), args.toArray(), extracotr);

		if(extracotr.getPaginatedList().isLastPage()){
			List<Object> argSum = new ArrayList<Object>();
			String sqlSum=querySumTotalPaymentByChannelCommissionSql(group.getGroupNo(), channel , code,  qryDateFrom,  qryDateTo,argSum);
			Map<String,Object> SumTotal=this.getJdbcTemplate().queryForMap(sqlSum.toString(), argSum.toArray());
			extracotr.getPaginatedList().setTotalObject(SumTotal);
		}
		return extracotr.getPaginatedList();


	}


	/** 組合以通路別跟CommissionCode查詢明細sql
	 * @param groupNo
	 * @param channel
	 * @param commissionCode
	 * @param adayS
	 * @param adayE
	 * @return
	 */
	public String getQueryPaymentByChannelCommission(String groupNo, String channel, String commissionCode, Date adayS, Date adayE,List<Object> args, boolean order)
	{
		StringBuffer sql = new StringBuffer();
		args.clear();

		sql.append("\r\n");
		sql.append("select group_no, group_name, virtual_account, payer_name, payer_id, tx_date, write_off_date, amount,barcdoe1 ").append("\r\n");
		sql.append("from(").append("\r\n");

		sql.append("select g.group_no, g.group_name, 1 as src_type, p.virtual_account, p.payer_name").append("\r\n");
		sql.append(", (case when g.group_type = 2 then p.student_id else p.payer_id end ) as payer_id").append("\r\n");
		sql.append(", w.tx_date, w.write_off_date, w.amount,p.barcode_stores_1 as barcdoe1 ").append("\r\n");
		sql.append("from (SELECT * FROM payment WHERE payment_status = 5 ").append("\r\n");

		sql.append(") AS p ,").append("\r\n");
		sql.append(" (select * from write_off_payment where 1=1 ").append("\r\n");
		if (StringUtils.isNotEmpty(channel))
		{
			sql.append("and channel = ? ").append("\r\n");
			args.add(channel);
		}
		else
		{
			sql.append("and channel in ('D', 'E', 'F', 'G', 'H')").append("\r\n");
		}

		if (null != adayS)
		{
			sql.append("and convert(varchar(8),write_off_date,112) between convert(varchar(8),?,112) and convert(varchar(8),?,112)  ").append("\r\n");
			args.add(adayS);
			args.add(adayE);
		}		
		sql.append(")as w, ").append("\r\n");

		sql.append("real_account r, app_group g, commission_code_table c ").append("\r\n");sql.append("where p.payment_pk = w.payment_pk").append("\r\n");
		sql.append("and p.group_pk = g.group_pk").append("\r\n");
		sql.append("and r.group_pk = g.group_pk").append("\r\n");
		sql.append("and substring(p.virtual_account,1, len(r.VIRTUAL_ACCOUNT_NO)) = r.VIRTUAL_ACCOUNT_NO").append("\r\n");
		sql.append("and c.commission_code  = (case when p.total_amount =w.amount then substring(p.barcode_stores_1 , 7, 3) else  (case  when p.barcode_stores_1_loan != null then  substring(p.barcode_stores_1_loan, 7, 3) else substring(p.barcode_stores_1, 7, 3)  end  )   end )").append("\r\n");


		if (StringUtils.isNotEmpty(commissionCode))
		{
			sql.append("and c.commission_code = ? ").append("\r\n");
			args.add(commissionCode);
		}

		if (StringUtils.isNotEmpty(groupNo))
		{
			sql.append("and g.GROUP_NO = ?").append("\r\n");
			args.add(groupNo);
		}

		sql.append("\r\n");
		// 四大超商，不含郵局
		if (StringUtils.isEmpty(channel) || ( StringUtils.isNotEmpty(channel) && !"H".equals(channel)) )
		{
			sql.append("union all ").append("\r\n");
			sql.append("\r\n");

			sql.append("select g.group_no, g.group_name, 2 as src_type, '銷帳失敗' as virtual_account, null as payer_name").append("\r\n");
			sql.append(", null as payer_id").append("\r\n");
			sql.append(", convert(datetime, f.aday,112) as tx_date, f.FBTIME as write_off_date, f.TXAMT as amount,f.barcdoe1").append("\r\n");
			sql.append("from (select * from feedback_log  ").append("\r\n");
			sql.append("where feedback_type = 2 ").append("\r\n");       
			sql.append("and barcdoe1 is not null  ").append("\r\n");
			sql.append("and err_msg is not null ").append("\r\n"); 
			if (StringUtils.isNotEmpty(channel))
			{
				sql.append("and channel = ? ").append("\r\n");
				if(channel.equals("D"))
					args.add("7111111");
				else if(channel.equals("E"))
					args.add("TFM");
				else if(channel.equals("F"))
					args.add("HILIFE");
				else if(channel.equals("G"))
					args.add("OKCVS");
				else if(channel.equals("H"))
					args.add("POST");
			}
			else
			{
				sql.append("and channel in ('7111111', 'TFM', 'HILIFE', 'OKCVS', 'POST')").append("\r\n");
			}

			if (null != adayS)
			{
				sql.append("and convert(varchar(8),fbtime,112) between ?  and ?  ").append("\r\n");
				args.add(adayS);
				args.add(adayE);
			}
			sql.append(")f ,real_account r, app_group g, commission_code_table c ").append("\r\n");
			sql.append("where r.group_pk = g.group_pk").append("\r\n");
			sql.append("and r.virtual_account_no = substring(f.barcdoe2, 1, len(r.virtual_account_no)) ").append("\r\n");
			sql.append("and c.commission_code  = substring(f.barcdoe1, 7, 3)").append("\r\n");

			if (StringUtils.isNotEmpty(groupNo))
			{
				sql.append("and g.GROUP_NO = ?").append("\r\n");
				args.add(groupNo);
			}

			if (StringUtils.isNotEmpty(commissionCode))
			{
				sql.append("and c.commission_code = ? ").append("\r\n");
				args.add(commissionCode);
			}


		}


		// 只有郵局
		if  ( StringUtils.isEmpty(commissionCode) && (StringUtils.isEmpty(channel) || ( StringUtils.isNotEmpty(channel) && "H".equals(channel)) ) )
		{
			sql.append("union all ").append("\r\n");
			sql.append("\r\n");

			sql.append("select g.group_no, g.group_name, 2 as src_type, '銷帳失敗' as virtual_account, null as payer_name").append("\r\n");
			sql.append(", null as payer_id").append("\r\n");
			sql.append(", convert(datetime, f.aday,112) as tx_date, f.FBTIME as write_off_date, f.TXAMT as amount,null as barcdoe1 ").append("\r\n");
			sql.append("from (select * from feedback_log  ").append("\r\n");
			sql.append("where feedback_type = 2 ").append("\r\n");       
			sql.append("and channel = 'POST'").append("\r\n");
			sql.append("and barcdoe3 is not null ").append("\r\n");
			sql.append("and err_msg is not null").append("\r\n");
			if (null != adayS)
			{
				sql.append("and convert(varchar(8),fbtime,112) between ?  and ?  ").append("\r\n");
				args.add(adayS);
				args.add(adayE);
			}
			sql.append(")f ,real_account r, app_group g, commission_code_table c ").append("\r\n");
			sql.append("where r.group_pk = g.group_pk").append("\r\n");
			sql.append("and r.virtual_account_no = substring(f.barcdoe3, 1, len(r.virtual_account_no)) ").append("\r\n");


			if (StringUtils.isNotEmpty(groupNo))
			{
				sql.append("and g.GROUP_NO = ?").append("\r\n");
				args.add(groupNo);
			}



		}

		sql.append("").append("\r\n");
		sql.append(") tempA").append("\r\n");
		if(order){
			sql.append("order by substring(barcdoe1,len(barcdoe1)-2,3), virtual_account").append("\r\n");
		}
		sql.append("\r\n");

		logger.debug("getQueryPaymentByChannelCommission SQL="+sql+"; args="+args);
		return sql.toString();  
	}


	/** 總計明細用的總計 
	 * 顯示在最下方,最後一頁再顯示
	 * 
	 * */
	public String querySumTotalPaymentByChannelCommissionSql(String groupNo, String channel, String commissionCode, Date adayS, Date adayE,List<Object> args){	
		StringBuffer sql = new StringBuffer();
		sql.append("select  count(*) as totalCount ,sum(amount) as totalAmount   from ( ");
		sql.append(getQueryPaymentByChannelCommission(groupNo, channel,commissionCode, adayS, adayE, args, false));
		sql.append(" )  a  where 1=1 ");		
		logger.debug("querySumTotalPaymentByChannelCommissionSql SQL="+sql+"; args="+args);

		/*釋放資源 */		
		return sql.toString();
	};



	class RowMapperResultSetExtractor<T> implements ResultSetExtractor<List<T>>
	{
		private RowMapper<T> rowMapper;
		private PaginatedList<T> paginatedList;

		public RowMapperResultSetExtractor(RowMapper<T> rowMapper, PaginatedList<T> paginatedList) {
			Assert.notNull(rowMapper, "RowMapper is required");
			Assert.notNull(paginatedList, "PaginatedList is required");
			this.rowMapper = rowMapper;
			this.paginatedList = paginatedList;
		}


		public List<T> extractData(ResultSet rs) throws SQLException {
			List<T> results = new ArrayList<T>();

			int maxResults = paginatedList.getPageSize(); //the maximum number of results
			int firstResult = paginatedList.getFirstIndexForCurrentPage();  //the first result to retrieve, numbered from 0
			int lastResult = firstResult + maxResults -1;  //the first result to retrieve, numbered from 0

			int rowNum = 0;
			while (rs.next()) {

				if (firstResult <= rowNum && lastResult >= rowNum)
					results.add(this.rowMapper.mapRow(rs, rowNum++));
				else
					rowNum++;
			}

			paginatedList.setFullListSize(rowNum);
			paginatedList.setPaginatedList(results);
			return results;

		}

		/**
		 * @return the paginatedList
		 */
		public PaginatedList<T> getPaginatedList() {
			return paginatedList;
		}
	}

}
